﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using MySql.Data.MySqlClient;

namespace WSDatos
{
    class clsCxpagar
    {
        //Devuelve los pagos referentes a una factura
        public DataTable ObtenerPagosCliente(int cve_cte)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";

            comando = @"SELECT f.CVE_CTE as cve,
(SELECT NOMBRE FROM clientes cl WHERE cl.CVE_CTE=f.CVE_CTE) as cliente,
f.CVE_FAC as NoFactura,
(SELECT DESCR FROM cve_cxc c WHERE c.TIP_DOC=p.TIP_DOC) as TipoPago,
p.NETO as TotalPago,
p.FEC_DOC as FechaPago,
f.NETO as TotalFact
FROM h_facts f inner join pagos p using(CVE_CIA)
WHERE TIP_REF=1 and SER_REF=SERIE and NUM_REF=CVE_FAC AND f.CVE_CTE=" + cve_cte;
            
            dtt = manejoInfo.FncSelecciona(comando, "PagosdeCliente");
            return dtt;
        }

        public DataTable ObtenerPagosFactura(int no_fact)
        { 
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";

            comando = @"SELECT f.CVE_CTE,
                        (SELECT NOMBRE FROM clientes cl WHERE cl.CVE_CTE=f.CVE_CTE) as cliente,
                        (SELECT DESCR FROM cve_cxc c WHERE c.TIP_DOC=p.TIP_DOC) as TipoPago,
                        p.NETO as TotalPago,
                        p.FEC_DOC as FechaPago,
                        f.NETO as TotalFact
                        FROM h_facts f inner join pagos p using(CVE_CIA)
                        WHERE TIP_REF=1 and SER_REF=SERIE and NUM_REF=CVE_FAC AND f.CVE_FAC=" + no_fact;
            
            dtt = manejoInfo.FncSelecciona(comando, "PagosdeFactura");
            return dtt;    
       }

        public DataTable ObtenerDoctosVencidos(int cve_cte)
        { 
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";

            comando = @"SELECT 'Factura' as Docto,substring(FEC_EMB,1,10) as FechaPago,NETO as PagoNeto FROM h_facts
                        WHERE  STATUS = 'F' AND FEC_EMB<NOW() AND
                        CVE_CTE=" + cve_cte;
            
            dtt = manejoInfo.FncSelecciona(comando, "DoctosVencidos");
            return dtt;    
       }

        public DataTable ObtenerDoctosActivos(int cve_cte)
        {
            DataTable dtt;
            clsNegocia manejoInfo = new clsNegocia();
            string comando = "";

            comando = @"SELECT 'Factura' as Docto,substring(FEC_EMB,1,10) as FechaPago,NETO as PagoNeto FROM h_facts
                        WHERE STATUS = 'F' AND FEC_EMB>=NOW() AND
                        CVE_CTE=" + cve_cte;

            dtt = manejoInfo.FncSelecciona(comando, "DoctosActivos");
            return dtt;
        }


    }
}